package com.jiudao.dao;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.google.gson.JsonArray;
import com.jiudao.entity.Applist;
import com.jiudao.entity.JylRiskconPerson;
import com.jiudao.entity.Riskcon;
import com.jiudao.entity.Risklist;

/**
 * 缴费年限分析dao层
 * 
 * @author gyj
 *
 */
@Repository
public class PaymentYearsAnalysisDao extends BaseDao {

	/**
	 * 保单缴费年限分布
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public JsonArray paymentYearAnalysis(String branch, String beginDate, String endDate) {
		String sql = " select " + applist.column(Applist.YEARNUM) + " YEARNUM, " + applist.column(Applist.PAYCODE)
				+ " PAYCODE " + " ,count(*) POLICYNO_COUNT " + " from " + riskcon.tablename() + " left join "
				+ applist.tablename() + " on " + riskcon.column(Riskcon.POLICYNO) + " = "
				+ applist.column(Applist.POLICYNO) + " and " + riskcon.column(Riskcon.CLASSCODE) + " = "
				+ applist.column(Applist.CLASSCODE) + " where 1=1 ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + riskcon.column(Riskcon.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " < '" + endDate + "'";
		}
		sql += " group by YEARNUM,PAYCODE";

		logger.info(sql);
		String columns = "YEARNUM,PAYCODE,POLICYNO_COUNT";
		return executeQuery(sql, columns);
	}

	/**
	 * 前十主力险种缴费年限分析
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public JsonArray topTenPaymentYearAnalysis(String branch, String beginDate, String endDate) {
		String sql = " select ntable.c COUNT_POLICYNO,ntable.classcode CLASSCODE,ntable.classname CLASSNAME,netable.cou COUNT_YEAR,netable.yearnum  YEARNUM,netable.paycode  PAYCODE from "
				+ " (select count(1) cou, " + applist.column(Applist.YEARNUM) + " yearnum, "
				+ applist.column(Applist.PAYCODE) + " paycode," + riskcon.column(Riskcon.CLASSCODE) + " classcode from "
				+ riskcon.tablename() + " left join " + jylRiskconPerson.tablename() + " on "
				+ riskcon.column(Riskcon.POLICYNO) + " = " + jylRiskconPerson.column(JylRiskconPerson.POLICYNO)
				+ " left join " + applist.tablename() + " on " + riskcon.column(Riskcon.POLICYNO) + " = "
				+ applist.column(Applist.POLICYNO) + " and " + riskcon.column(Riskcon.CLASSCODE) + " = "
				+ applist.column(Applist.CLASSCODE) + " where " + jylRiskconPerson.column(JylRiskconPerson.LONG_RISK)
				+ "='长险'" + " and " + riskcon.column(Riskcon.APPF) + " ='1' ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + riskcon.column(Riskcon.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " < '" + endDate + "'";
		}
		sql += " group by " + applist.column(Applist.YEARNUM) + " , " + applist.column(Applist.PAYCODE) + ","
				+ riskcon.column(Riskcon.CLASSCODE) + " )as netable " + " inner join " + " (select count(1) c, "
				+ riskcon.column(Riskcon.CLASSCODE) + " classcode, " + risklist.column(Risklist.CLASSNAME)
				+ " classname from " + riskcon.tablename() + " left join " + jylRiskconPerson.tablename() + " on "
				+ riskcon.column(Riskcon.POLICYNO) + " = " + jylRiskconPerson.column(JylRiskconPerson.POLICYNO)
				+ " left join " + risklist.tablename() + " on " + riskcon.column(Riskcon.CLASSCODE) + " = "
				+ risklist.column(Risklist.CLASSCODE) + " where " + jylRiskconPerson.column(JylRiskconPerson.LONG_RISK)
				+ "='长险'" + " and " + riskcon.column(Riskcon.APPF) + " ='1' ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + riskcon.column(Riskcon.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " < '" + endDate + "'";
		}
		sql += " group by " + riskcon.column(Riskcon.CLASSCODE) + " , " + risklist.column(Risklist.CLASSNAME)
				+ " )as ntable " + " on ntable.classcode = netable.classcode "
				+ " order by COUNT_POLICYNO desc,COUNT_YEAR desc " + " limit 10 ";

		logger.info(sql);
		String columns = "COUNT_POLICYNO,CLASSCODE,CLASSNAME,COUNT_YEAR,YEARNUM,PAYCODE";
		return executeQuery(sql, columns);
	}

	/**
	 * 四大客群主要缴费年限
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public JsonArray fourTypePaymentYearAnalysis(String branch, String beginDate, String endDate, String personMark) {
		String sql = " select " + applist.column(Applist.YEARNUM) + "," + applist.column(Applist.PAYCODE)
				+ " ,count(1) POLICYNO_COUNT from " + riskcon.tablename() + " left join " + jylRiskconPerson.tablename()
				+ " on " + riskcon.column(Riskcon.POLICYNO) + " = " + jylRiskconPerson.column(JylRiskconPerson.POLICYNO)
				+ " left join " + applist.tablename() + " on " + riskcon.column(Riskcon.POLICYNO) + " = "
				+ applist.column(Applist.POLICYNO) + " and " + riskcon.column(Riskcon.CLASSCODE) + " = "
				+ applist.column(Applist.CLASSCODE) + " where 1=1 ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + riskcon.column(Riskcon.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " < '" + endDate + "'";
		}
		if (!StringUtils.isEmpty(personMark)) {
			sql += " and " + jylRiskconPerson.column(JylRiskconPerson.PERSON_MARK) + " = '" + personMark + "'";
		}
		sql += " group by " + applist.column(Applist.YEARNUM) + "," + applist.column(Applist.PAYCODE);

		logger.info(sql);
		String columns = Applist.YEARNUM + "," + Applist.PAYCODE + ",POLICYNO_COUNT";
		return executeQuery(sql, columns);
	}

	/**
	 * 四大客群前五险种缴费分布
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public JsonArray fourTypeTopFiveAnalysis(String branch, String beginDate, String endDate, String personMark) {
		String sql = " select " + risklist.column(Risklist.CLASSCODE) + " , " + risklist.column(Risklist.CLASSNAME)
				+ ",count(1) COUNT_POLICYNO from " + riskcon.tablename() + " left join " + jylRiskconPerson.tablename()
				+ " on " + riskcon.column(Riskcon.POLICYNO) + " = " + jylRiskconPerson.column(JylRiskconPerson.POLICYNO)
				+ " left join " + risklist.tablename() + " on " + riskcon.column(Riskcon.CLASSCODE) + " = "
				+ risklist.column(Risklist.CLASSCODE) + " where 1=1 ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + riskcon.column(Riskcon.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " < '" + endDate + "'";
		}
		if (!StringUtils.isEmpty(personMark)) {
			sql += " and " + jylRiskconPerson.column(JylRiskconPerson.PERSON_MARK) + " = '" + personMark + "'";
		}
		sql += " group by " + risklist.column(Risklist.CLASSCODE) + " , " + risklist.column(Risklist.CLASSNAME)
				+ " order by COUNT_POLICYNO desc " + " limit 5 ";

		logger.info(sql);
		String columns = Risklist.CLASSCODE + "," + Risklist.CLASSNAME + ",COUNT_POLICYNO";
		return executeQuery(sql, columns);
	}

	/**
	 * 四大客群前五险种缴费分布联动饼图（展示缴费年限）
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDate
	 * @param persomMark
	 * @param classcode
	 * @return
	 */
	public JsonArray fourTypeTopFivePaymentYearAnalysis(String branch, String beginDate, String endDate,
			String personMark, String classcode) {
		String sql = " select " + applist.column(Applist.YEARNUM) + "," + applist.column(Applist.PAYCODE)
				+ " ,count(*) POLICYNO_COUNT " + "from " + riskcon.tablename() + " left join "
				+ jylRiskconPerson.tablename() + " on " + riskcon.column(Riskcon.POLICYNO) + " = "
				+ jylRiskconPerson.column(JylRiskconPerson.POLICYNO) + " left join " + applist.tablename() + " on "
				+ riskcon.column(Riskcon.POLICYNO) + " = " + applist.column(Applist.POLICYNO) + " and "
				+ riskcon.column(Riskcon.CLASSCODE) + " = " + applist.column(Applist.CLASSCODE) + " where 1 = 1 ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + riskcon.column(Riskcon.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + riskcon.column(Riskcon.APPDATE) + " < '" + endDate + "'";
		}
		if (!StringUtils.isEmpty(personMark)) {
			sql += " and " + jylRiskconPerson.column(JylRiskconPerson.PERSON_MARK) + " = '" + personMark + "'";
		}
		;
		if (!StringUtils.isEmpty(classcode)) {
			classcode = classcode.substring(0, classcode.length() - 1);
			sql += " and " + riskcon.column(Riskcon.CLASSCODE) + " in(" + classcode + ")";
		}
		;

		sql += " group by " + applist.column(Applist.YEARNUM) + "," + applist.column(Applist.PAYCODE);

		logger.info(sql);
		String columns = Applist.YEARNUM + "," + Applist.PAYCODE + ",POLICYNO_COUNT";
		return executeQuery(sql, columns);
	}
}
